﻿/*
 * Class Description            
 *                                 
 *                              
 * 
 * 
 * 
 */ 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;

namespace InControl
{
    public class AppControl
    {
        private int _Stat = 0;
        private int _Pid = -1;
        //private int _Group = -1;
        private string _Group = "";
        private String qString;
        DBDriver db;


        /// <summary>
        /// 
        /// </summary>
        public AppControl()
        {
            //db = DBDriver.GetInstance;
            //db.CreateSqlConnection();
        }

        
        /// <summary>
        /// 
        /// </summary>
        public int stat
        {
            set {
                this._Stat = value;
            }
            get { return this._Stat; }
        }

        
        /// <summary>
        /// 
        /// </summary>
        public int pid
        {
            set {
                this._Pid = value;
            }
            get { return this._Pid; }

        }


        ///// <summary>
        ///// 
        ///// </summary>
        //public int group
        //{
        //    set
        //    {
        //        this._Group = value;
        //    }
        //    get { return this._Group; }

        //}

        /// <summary>
        /// 
        /// </summary>
        public string group
        {
            set
            {
                this._Group = value;
            }
            get { return this._Group; }

        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string GetStringList()
        {
            List<String> l = new List<String>();
            qString = "SELECT [fname] ,[lname], [mname] FROM [inDB].[dbo].[person_table] WHERE [pid] = '" + pid + "'";
            db = DBDriver.GetInstance;
            return db.GetString(qString, 3);
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string getfullName()
        {
            //db.CreateSqlConnection();
            qString = "SELECT [lname] ,[fname], [mname] FROM [inDB].[dbo].[person_table] WHERE [pid] = '" + pid + "'";
            String s = db.GetString(qString, 3);
            //db.CloseSqlConnection();
            return s;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="a"></param>
        /// <param name="b"></param>
        /// <returns></returns>
        public Boolean Authenticate(String a, String b)
        {
            //int i = db.Authenticate(a, b); //db.CreateSqlConnection();
            //return i; //db.CloseSqlConnection();
            db = DBDriver.GetInstance;
            db.CreateSqlConnection();
            List<String> ls = db.Authenticate(a, b);
            if (ls != null)
            {
                pid =  Convert.ToInt32(ls[0]);
                //group = Convert.ToInt32(ls[1]);
                group = ls[1].ToString();

                return true;
            }
            return false;      
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="iname"></param>
        /// <param name="price"></param>
        /// <param name="cat"></param>
        /// <param name="type"></param>
        /// <param name="loc"></param>
        /// <param name="quan"></param>
        public void recordNewItem(String iname, String price, String cat, String type, String loc, String quan)
        {
            int c, t, l;

            qString = "INSERT INTO [inDB].[dbo].[cat_table] ([category]) VALUES ('" + cat + "'); Select inDB.dbo.cat_table.id from [inDB].[dbo].[cat_table] WHERE category = '" + cat + "'";
            c = Convert.ToInt32(db.GetString(qString, 1));

            qString = "INSERT INTO [inDB].[dbo].[type_table] ([type_]) VALUES ('" + type + "'); Select inDB.dbo.type_table.id from [inDB].[dbo].[type_table] WHERE type_ = '" + type + "'";
            t = Convert.ToInt32(db.GetString(qString, 1));

            qString = "INSERT INTO [inDB].[dbo].[inven_table] ([location]) VALUES ('" + loc + "'); Select inDB.dbo.inven_table.id from [inDB].[dbo].[inven_table] WHERE location = '" + loc + "'";

            l = Convert.ToInt32(db.GetString(qString, 1));

            qString = "INSERT INTO [inDB].[dbo].[item_table] ([price], [name], [cat_id], [typ_id], [inv_id])" +
                        "VALUES (" + price + ", '" + iname + "', " + c + "," + t + "," + l + ")";
            db.GodInsert(qString);

            qString = "SELECT [quantity] FROM [inDB].[dbo].[item_table]  WHERE [name] = '" + iname + "' AND [typ_id] = " + t + " AND [cat_id] = " + c + "";
            l = Convert.ToInt32(db.GetString(qString, 1)) + Convert.ToInt32(quan);

            qString = "UPDATE [inDB].[dbo].[item_table] SET [quantity] = " + l + " WHERE [name] = '" + iname + "' AND [typ_id] = " + t + " AND [cat_id] = " + c + "";
            db.GodInsert(qString);       
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="iname"></param>
        /// <param name="price"></param>
        /// <param name="cat"></param>
        /// <param name="type"></param>
        /// <param name="loc"></param>
        /// <param name="quan"></param>
        public void UpdateItem(String id, String iname, String price, String cat, String type, String loc, String quan)
        {
             qString = "UPDATE [inDB].[dbo].[item_table] SET [quantity] = " + Convert.ToInt32(quan) + "," +
                "[price] = " + price + "," +
                "[name] = '" + iname + "'," +
                "[cat_id] = " + Convert.ToInt32(cat) + "," +
                "[typ_id] = " + Convert.ToInt32(type) + "," +
                "[inv_id] = " + Convert.ToInt32(loc) + 
                " WHERE [id] = " + Convert.ToInt32(id) + ";";
            db.GodInsert(qString);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="iname"></param>
        /// <param name="cat"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int GetStock(String iname, String cat, String type )
        {
            //db.CreateSqlConnection();
            int a, b;
            qString = "SELECT [id] FROM [inDB].[dbo].[cat_table] WHERE [category] = '" + cat + "'";
            a = Convert.ToInt32(db.GetString(qString, 1));
            qString = "SELECT [id] FROM [inDB].[dbo].[type_table] WHERE [type_] = '" + type + "'";
            b = Convert.ToInt32(db.GetString(qString, 1));
            qString = "SELECT [quantity] FROM [inDB].[dbo].[item_table]  WHERE [name] = '" + iname + "' AND [typ_id] = " + b + " AND [cat_id] = " + a + "";
            int i = Convert.ToInt32(db.GetString(qString, 1));
            //db.CloseSqlConnection();
            return i;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public List<String> checkOutGrid(String id, int c)
        {
            qString = "SELECT [price], [cat_id], [typ_id] FROM [inDB].[dbo].[item_table] WHERE id = " + id + ";";
            List<String> ls = db.GetList(qString, 3);
            qString = "SELECT [category] FROM [inDB].[dbo].[cat_table] WHERE id = " + ls[1] + ";";
            String s = db.GetString(qString, 1);
            ls[1] = s;
            qString = "SELECT [type_] FROM [inDB].[dbo].[type_table] WHERE id = " + ls[2] + ";";
            s = db.GetString(qString, 1);
            ls[2] = s;
            
            return ls;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dGV"></param>
        //public void CheckOutGridSave(String id, String d, DataGridView dGV)
        public void CheckOutGridSave(String id, DataGridView dGV)
        {
            String sqlString;
            int i;

            sqlString = "SELECT MAX(chkout_id) FROM inDB.dbo.pchkout_table";

            String cnt = db.GetString(sqlString, 1);

            if (cnt == "")
                i = 0;
            else
                i = Convert.ToInt32(cnt);
            ++i;

            foreach (DataGridViewRow dgvr in dGV.Rows)
            {
                if (!dgvr.IsNewRow)
                {
                    if (!dgvr.ReadOnly)
                    {
                        if (!CheckStock(dgvr.Cells["item"].Value.ToString(), dgvr.Cells["_Quantity"].Value.ToString()))
                        {
                            dgvr.Selected = true;
                            throw new Exception("Quota Exceeded: " + dgvr.Cells["Item"].Value.ToString());
                        }
                        sqlString = "INSERT INTO [inDB].[dbo].[pchkout_table](chkout_id, p_id, date) " +
                                    "VALUES('" + i + "', '" + id + "', '" + dgvr.Cells["_Date"].Value.ToString() + "');";
                        db.GodInsert(sqlString);
                        sqlString = "INSERT INTO [inDB].[dbo].[chkout_table](chkout_id, i_id, quantity) " +
                                     "VALUES('" + i + "', '" + dgvr.Cells["Item"].Value.ToString() + "', '" + dgvr.Cells["_Quantity"].Value.ToString() + "');";
                        db.GodInsert(sqlString);
                        dgvr.ReadOnly = true;
                    }
                }

            }
        }
        
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="iname"></param>
        /// <param name="cat"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int GetQuantity(String iname, String cat, String type)
        {
            qString = "SELECT [quantity] FROM [inDB].[dbo].[item_table] WHERE [name] = '" + iname + "' AND [typ_id] = " + type + " AND [cat_id] = " + cat + "";
            int i = Convert.ToInt32(db.GetString(qString, 1));
            return i;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="q"></param>
        /// <returns></returns>
        public Boolean CheckStock(String iid, String q)
        {
            int i = GetQuantity(iid);
            if (Convert.ToInt32(q) > i)
            {
                //return false;
                throw new Exception("CHECKOUT ERROR \nITEM ID: " + iid + "(" + iid + ") stock exceeded. Cannot complete request.");
            }
            else
                return true;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="itemid"></param>
        /// <returns></returns>
        public int GetQuantity(String itemid)
        {
            qString = "SELECT [quantity] FROM [inDB].[dbo].[item_table] WHERE [id] = '" + itemid + "'";
            int i = Convert.ToInt32(db.GetString(qString, 1));
            //db.CloseSqlConnection();
            return i;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="_cb"></param>
        /// <param name="_sTable"></param>
        /// <param name="_sColumn"></param>
        /// <param name="_sMember"></param>
        /// <param name="f"></param>
        public void FillCombo2(DataGridViewComboBoxColumn _cb, string _sTable, string _sColumn, string _sMember, int f)
        {
            SqlDataAdapter dAdapter = new SqlDataAdapter();

            if (f == 0)
            {
                dAdapter = new SqlDataAdapter("SELECT 0 s,  0 AS " + _sMember + ", 'Please Select...' AS " + _sColumn + " " +
                                                "UNION Select 1 s, " + _sMember + ", " + _sColumn + " " + "FROM [inDB].[dbo].[" + _sTable + "]" +
                                                "ORDER BY s, " + _sColumn + ";", db.GetCon());
            }
            else if (f == 1) //lastname firstname combination
            {
                dAdapter = new SqlDataAdapter("SELECT 0 s,  0 AS " + _sMember + ", 'Please Select...' AS cname " +
                                                "UNION Select 1 s, " + _sMember + ", (" + _sColumn + ") cname FROM [inDB].[dbo].[" + _sTable + "] " +
                                                "ORDER BY s, cname;", db.GetCon());
            }

            DataTable _dtSource = new DataTable();
            dAdapter.Fill(_dtSource);
            _cb.DataSource = _dtSource;
            if (f == 0)
                _cb.DisplayMember = _sColumn;
            else if (f == 1)
                _cb.DisplayMember = "cname";
            _cb.ValueMember = _sMember;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="_cb"></param>
        /// <param name="_sTable"></param>
        /// <param name="_sColumn"></param>
        /// <param name="_sMember"></param>
        public void FillCombo(ComboBox _cb, string _sTable, string _sColumn, string _sMember, int f)
        {
            SqlDataAdapter dAdapter = new SqlDataAdapter();

            if (f == 0)
            {
                dAdapter = new SqlDataAdapter("SELECT 0 s,  0 AS " + _sMember + ", 'Please Select...' AS " + _sColumn + " " +
                                                "UNION Select 1 s, " + _sMember + ", " + _sColumn + " " + "FROM [inDB].[dbo].[" + _sTable + "]" +
                                                "ORDER BY s, " + _sColumn + ";", db.GetCon());
            }
            else if (f == 1) //lastname firstname combination
            {
                if(_Group == "administrators")
                    dAdapter = new SqlDataAdapter("SELECT 0 s,  0 AS " + _sMember + ", 'Please Select...' AS cname " +
                                                "UNION Select 1 s, " + _sMember + ", (" + _sColumn + ") cname FROM [inDB].[dbo].[" + _sTable + "] " +
                                                "ORDER BY s, cname;", db.GetCon());
                else
                    dAdapter = new SqlDataAdapter("SELECT 0 s,  0 AS " + _sMember + ", 'Please Select...' AS cname " +
                                                "UNION Select 1 s, " + _sMember + ", (" + _sColumn + ") cname FROM [inDB].[dbo].[" + _sTable + "] " +
                                                "WHERE pid = '" + _Pid + "'" +
                                                "ORDER BY s, cname;", db.GetCon());
            }
            DataTable _dtSource = new DataTable();
            dAdapter.Fill(_dtSource);
            _cb.DataSource = _dtSource;
            if (f == 0)
                _cb.DisplayMember = _sColumn;
            else if (f == 1)
                _cb.DisplayMember = "cname";
            _cb.ValueMember = _sMember;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cl"></param>
        /// <param name="tl"></param>
        /// <param name="id"></param>
        /// <param name="f"></param>
        public void ComboList(List<Label> ll, List<ComboBox> cl, List<TextBox> tl, String id, int f)
        {
            List<String> sl;
            if (f == 3)
            {
                qString = "SELECT [cat_id], [typ_id], [inv_id], [price], [quantity] FROM [inDB].[dbo].[item_table] WHERE id = " + id + ";";
                sl = db.GetList(qString, 5);

                cl[0].SelectedValue = sl[0];
                cl[1].SelectedValue = sl[1];
                cl[2].SelectedValue = sl[2];
                tl[0].Text = sl[3];
                tl[1].Text = sl[4];
            }
            else if (f == 2)
            {
                qString = "SELECT [ework], [ext] FROM [inDB].[dbo].[person_table] WHERE pid = " + id + ";";
                sl = db.GetList(qString, 2);

                ll[0].Text = sl[0]; 
                ll[1].Text = sl[1];
     
                DateTime dt = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day);
                DateTimeOffset dto = new DateTimeOffset(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, DateTime.Today.Hour, DateTime.Today.Minute, DateTime.Today.Second, TimeSpan.Zero);
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataGridView1"></param>
        public void FillTable(DataGridView dataGridView1, String op)
        { 
            String s;
            if (op == "-1")
            {
                s = "SELECT I.name, C.category, T.type_, I.quantity, I.price " +
                    "FROM inDB.dbo.item_table I " +
                    "JOIN inDB.dbo.cat_table C ON I.cat_id = C.id " +
                    "JOIN inDB.dbo.type_table T ON I.typ_id = T.id;";;
            }
            else if (op == "-2")
            {
                if (_Group == "administrators")
                    s = "Select pid, (lname + ', ' + fname + ' ' + mname) cname FROM [inDB].[dbo].[person_table] ORDER BY lname"; 
                else
                    s = "Select pid, (lname + ', ' + fname + ' ' + mname) cname FROM [inDB].[dbo].[person_table] WHERE pid = '" + pid + "' ORDER BY lname"; 
            }
            else
            {
                s = "SELECT I.id, O.quantity, I.price, C.category, T.type_, PC.date " +
                    "FROM inDB.dbo.item_table I " +
                    "JOIN inDB.dbo.chkout_table O ON O.i_id = I.id " +
                    "JOIN inDB.dbo.pchkout_table PC ON O.chkout_id = PC.chkout_id " +
                    "JOIN inDB.dbo.cat_table C ON I.cat_id = C.id " +
                    "JOIN inDB.dbo.type_table T ON I.typ_id = T.id " +
                    "WHERE PC.p_id = '" + op + "';";
            }

            SqlDataAdapter adapter = new SqlDataAdapter(s,db.GetCon());

            //Create a DataSet object
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();

            //filling the DataSet 
            adapter.Fill(dt);
            ds.Tables.Add(dt);

            // you can use one of the followings.Both work fine.
            // this.dataGrid1.SetDataBinding(ds,"MyTable"); or
            dataGridView1.DataSource = ds.Tables[dt.ToString()];
            for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
            {
                dataGridView1.Rows[i].ReadOnly = true;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="date"></param>
        public void DeleteGridItem(String iid, String date)
        {
            qString = "DELETE FROM dbo.chkout_table WHERE chkout_id in " +
                            "(SELECT chkout_id from dbo.pchkout_table WHERE date = '" + date + "'" +
                                "AND i_id = '" + iid + "')";
            db.GodInsert(qString);

            qString = "SELECT COUNT (*) FROM inDB.dbo.chkout_table";

            if (db.GetString(qString, 1) == "0")
            {
                qString = "DELETE FROM [inDB].[dbo].[pchkout_table] WHERE date = '" + date + "'";
                db.GodInsert(qString);
            } 
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="date"></param>
        /// <param name="newVal"></param>
        public void updateGridItem(String iid, String date, String newVal)
        {
            if (CheckStock(iid, newVal))
            {
                qString = "UPDATE [inDB].[dbo].[chkout_table] " +
                            "SET [quantity] = '" + newVal + "'" +
                            "WHERE chkout_id in (SELECT chkout_id from [inDB].[dbo].[pchkout_table] " +
                            "WHERE date = '" + date + "'" +
                            "AND i_id = '" + iid + "')";
                db.GodInsert(qString);
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="title"></param>
        /// <param name="ext"></param>
        /// <param name="mobile"></param>
        /// <param name="home"></param>
        /// <param name="ework"></param>
        /// <param name="epersonal"></param>
        /// <param name="passw"></param>
        public void AddNewEmployee(String fname, String lname, String mname, String title, String ext, String mobile, String ework, String epersonal, String passw)
        {
            qString = "INSERT INTO [inDB].[dbo].[person_table] ([fname] ,[lname] ,[mname] ,[title] ,[ext] ,[mobile], " +
                        "[ework] ,[epersonal] ,[passw], [groupid]) " +
                        "VALUES" +
                        "('" + fname + "', '" + lname + "', '" + mname + "', '" + title + "', '" + ext + "', '" + mobile + "', '" + ework + "', '" + epersonal + "', PWDENCRYPT ( '" + passw + "' ), 2)";

            db.GodInsert(qString);
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="title"></param>
        /// <param name="ext"></param>
        /// <param name="mobile"></param>
        /// <param name="ework"></param>
        /// <param name="epersonal"></param>
        /// <param name="passw"></param>
        public void UpdateEmployee(String pid, String fname, String lname, String mname, String title, String ext, String mobile, String ework, String epersonal, String passw)
        {
            qString = "UPDATE [inDB].[dbo].[person_table] " +
                      "SET [fname] = '" + fname + "', [lname] = '" + lname + "', [mname] = '" + mname + "', [title] = '" + title + "', " +
                      "[ext] = '" + ext + "', [mobile] = '" + mobile + "', [ework] = '" + ework + "', [epersonal] = '" + epersonal + "', " + 
                      "[passw] = PWDENCRYPT ( '" + passw + "' )" +
                      
                      "WHERE pid = '" + pid + "'";

            db.GodInsert(qString);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public String gridItemTotals(String pid)
        {
            qString = "SELECT SUM(quantity) FROM inDB.dbo.chkout_table " +
                        "WHERE chkout_id in (SELECT chkout_id from [inDB].[dbo].[pchkout_table] WHERE p_id = '" + pid + "')";
            return db.GetString(qString, 1);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public String GetCheckOuts(String pid)
        {
            qString = "SELECT COUNT(chkout_id) FROM inDB.dbo.pchkout_table WHERE p_id = '" + pid + "'";
            return db.GetString(qString, 1);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<String> EditPerson(String id)
        {
            List<String> ls;
            qString = "SELECT fname, lname, mname, title, ext, mobile, ework, epersonal from inDB.dbo.person_table where pid = '" + id + "'";
            ls = db.GetList(qString, 8);

            return ls;
        }

        public void CloseConnection()
        {
            db.CloseSqlConnection();
            db = null;
        }
    }
}
    